package cn.krui.helper.tools;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author zc
 */
public class ExcelHelper {
  private Map<String, CellStyle> styles;
  
  ExcelFileType fileType;
  
  Workbook book;
  
  boolean isInit;
  
  public ExcelHelper(ExcelFileType fileType) {
    this.book = null;
    this.isInit = false;
    this.fileType = fileType;
    this.styles = new HashMap<>();
  }
  
  public static Object readFromCell(Cell cell) {
    CellType type = cell.getCellType();
    if (type == CellType.FORMULA){
      type = cell.getCachedFormulaResultType(); 
    }
    switch (type) {
      case STRING:
        return cell.getRichStringCellValue().getString();
      case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)){
          return cell.getDateCellValue(); 
        }
        return Double.valueOf(cell.getNumericCellValue());
      case BOOLEAN:
        return Boolean.valueOf(cell.getBooleanCellValue());
      case FORMULA:
        return cell.getCellFormula();
      case ERROR:
        return Byte.valueOf(cell.getErrorCellValue());
      default:
        break;
    } 
    return null;
  }
  
  public static void writeToCell(Cell cell, Object value) {
    if (value == null) {
      cell.setBlank();
    } else if (value instanceof Integer) {
      cell.setCellValue(((Integer)value).intValue());
    } else if (value instanceof Double) {
      cell.setCellValue(((Double)value).doubleValue());
    } else if (value instanceof Byte) {
      cell.setCellValue(((Byte)value).byteValue());
    } else if (value instanceof BigInteger) {
      cell.setCellValue(((BigInteger)value).doubleValue());
    } else if (value instanceof BigDecimal) {
      cell.setCellValue(((BigDecimal)value).doubleValue());
    } else if (value instanceof Short) {
      cell.setCellValue(((Short)value).shortValue());
    } else if (value instanceof Boolean) {
      cell.setCellValue(((Boolean)value).booleanValue());
    } else if (value instanceof Date) {
      cell.setCellValue((Date)value);
    } else if (value instanceof Calendar) {
      cell.setCellValue((Calendar)value);
    } else if (value instanceof RichTextString) {
      cell.setCellValue((RichTextString)value);
    } else {
      cell.setCellValue(value.toString());
    } 
  }
  
  void init() {
    if (this.book == null){
      switch (this.fileType) {
        case xls:
          this.book = (Workbook)new HSSFWorkbook();
          break;
        case xlsx:
          this.book = (Workbook)new XSSFWorkbook();
          break;
      }
    }
    if (!this.isInit) {
      this.isInit = true;
      CellStyle style = createStyle("default.cell.style.name");
      setBorderAndAlign(style);
      style = createStyle("default.header.style.name");
      setBorderAndAlign(style);
      Font font = this.book.createFont();
      font.setBold(true);
      style.setFont(font);
      style = createStyle("default.date.style.name");
      setBorderAndAlign(style);
      DataFormat fmt = this.book.createDataFormat();
      style.setDataFormat(fmt.getFormat("yyyy\"\"m\"\"d\"\""));
    } 
  }
  
  void setBorderAndAlign(CellStyle style) {
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBorderLeft(BorderStyle.THIN);
    style.setBorderRight(BorderStyle.THIN);
    style.setBorderTop(BorderStyle.THIN);
  }
  
  public Font createFont() {
    init();
    return this.book.createFont();
  }
  
  public DataFormat createDataFormat() {
    init();
    return this.book.createDataFormat();
  }
  
  public CellStyle createStyle(String name) {
    init();
    CellStyle style = this.book.createCellStyle();
    this.styles.put(name, style);
    return style;
  }
  
  public ResultHandler<Map<String, Object>> export(IExcelSheetNameFormatter sheetNameFmt, ExcelColumn[][] columns, IExcelOriginPostionHandler postionHandler, IExcelSheetMaxRowsHandler maxRowsHandler, final IExcelProgressNotifyHandler notify, IExcelRowStyleHandler rowStyleHandler, boolean autoGenerateHeaders, short[] headerHeights) {
    init();
    final ExcelContext context = new ExcelContext(this.book, sheetNameFmt, columns, rowStyleHandler, postionHandler, 
        maxRowsHandler, this.styles, autoGenerateHeaders, headerHeights);
    return new ResultHandler<Map<String, Object>>() {
        public void handleResult(ResultContext<? extends Map<String, Object>> resultContext) {
          Map<String, Object> row = (Map<String, Object>)resultContext.getResultObject();
          context.writeRows(resultContext.getResultCount(), row);
          if (notify != null && notify.notify(resultContext.getResultCount())){
              resultContext.stop(); 
            }
        }
      };
  }
  
  public ResultHandler<Map<String, Object>> export() {
    return export(null, null, null, null, null, null, true, null);
  }
  
  public ResultHandler<Map<String, Object>> export(ExcelColumn[][] columns) {
    return export(null, columns, null, null, null, null, true, null);
  }
  
  public ResultHandler<Map<String, Object>> export(IExcelProgressNotifyHandler notify) {
    return export(null, null, null, null, notify, null, true, null);
  }
  
  public ResultHandler<Map<String, Object>> export(ExcelColumn[][] columns, IExcelProgressNotifyHandler notify) {
    return export(null, columns, null, null, notify, null, true, null);
  }
  
  public ResultHandler<Map<String, Object>> export(String sheetName) {
    return export(sheetName, (ExcelColumn[][])null);
  }
  
  public ResultHandler<Map<String, Object>> export(String sheetName, ExcelColumn[][] columns) {
    return export(sheetName, (ExcelColumn[][])null);
  }
  
//  public ResultHandler<Map<String, Object>> export(String sheetName, ExcelColumn[][] columns, IExcelProgressNotifyHandler notify) {
//    return export(i -> (i == 0) ? paramString : (String.valueOf(paramString) + i), columns, null, i -> (this.fileType == ExcelFileType.xls) ? 65535 : -1, 
//        notify, null, true, null);
//  }
//  
  protected void finalize() {
    if (this.book != null){
      try {
        this.book.close();
      } catch (IOException e) {
        e.printStackTrace();
      }   
    }
  }
  
  public void saveAs(OutputStream stream) throws IOException {
    if (this.book != null){
      this.book.write(stream); 
    }
  }
  
  public void saveAs(String fileName) throws IOException {
    if (this.book != null) {
    }
  }
  
  public void load(String file) throws EncryptedDocumentException, IOException {
    this.book = WorkbookFactory.create(new File(file));
  }
  
  public void load(InputStream fileStream) throws EncryptedDocumentException, IOException {
    this.book = WorkbookFactory.create(fileStream);
  }
  
  public int getNumberOfSheets() {
    if (this.book != null){
      return this.book.getNumberOfSheets(); 
    }
    return 0;
  }
  
  public String[] getSheets() {
    if (this.book != null) {
      String[] sheetNames = new String[this.book.getNumberOfSheets()];
      for (int i = 0; i < sheetNames.length; i++){
        sheetNames[i] = this.book.getSheetName(i); 
      }
      return sheetNames;
    } 
    return null;
  }
  
  public Sheet getSheet(String sheetName) {
    if (this.book != null){
      return this.book.getSheet(WorkbookUtil.createSafeSheetName(sheetName)); 
    }
    return null;
  }
  
  public Sheet getSheet(int sheetIndex) {
    if (this.book != null){
      return this.book.getSheetAt(sheetIndex); 
    }
    return null;
  }
  
  public Sheet createSheet(String sheetName) {
    init();
    return this.book.createSheet(WorkbookUtil.createSafeSheetName(sheetName));
  }
  
  public Sheet createSheet() {
    init();
    return this.book.createSheet();
  }
  
  public CellStyle createCellStyle() {
    if (this.book != null){
      return this.book.createCellStyle(); 
    }
    return null;
  }
  
  public void write(Sheet sheet, int rowIndex, int colIndex, Object value, CellStyle style) {
    Row row = sheet.getRow(rowIndex);
    if (row == null){
      row = sheet.createRow(rowIndex); 
    }
    Cell cell = row.getCell(colIndex);
    if (cell == null){
      cell = row.createCell(colIndex); 
    }
    if (style != null){
      cell.setCellStyle(style); 
    }
    writeToCell(cell, value);
  }
  
  public void mergeCells(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
    CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
    sheet.addMergedRegion(region);
  }
  
  public void unMergeCells(Sheet sheet, int firstRow, int firstCol) {
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
      CellRangeAddress region = sheet.getMergedRegion(i);
      if (region.getFirstRow() == firstRow && region.getFirstColumn() == firstCol) {
        sheet.removeMergedRegion(i);
        return;
      } 
    } 
  }
  
  public void read(Sheet sheet, ExcelReadHandler readHandler) {
    if (sheet != null){
      for (Row row : sheet) {
        Map<Integer, Object> data = new LinkedHashMap<>();
        for (Cell cell : row){
          data.put(Integer.valueOf(cell.getColumnIndex()), readFromCell(cell)); 
        }
        if (readHandler.read(row.getRowNum(), data, sheet.getSheetName(), sheet)){
          break; 
        }
      }  
    }
  }
  
  public void read(Sheet sheet, Map<String, String> columnMapping, ExcelReadWithHeaderHandler readHandler) {
    if (sheet != null) {
      Map<Integer, String> columns = null;
      for (Row row : sheet) {
        if (columns == null) {
          columns = new HashMap<>();
          for (Cell cell : row) {
            String headerName = readFromCell(cell).toString();
            if (columnMapping != null && columnMapping.containsKey(headerName)){
              headerName = columnMapping.get(headerName); 
            }
            columns.put(Integer.valueOf(cell.getColumnIndex()), headerName);
          } 
          continue;
        } 
        Map<String, Object> data = new LinkedHashMap<>();
        for (Cell cell : row){
          data.put(columns.get(Integer.valueOf(cell.getColumnIndex())), readFromCell(cell)); 
        }
        if (readHandler.read(row.getRowNum(), data, sheet.getSheetName(), sheet)){
          break; 
        }
      } 
    } 
  }
  
  public void read(Sheet sheet, int firstRow, int firstCol, int lastRow, int lastCol, ExcelReadHandler readHandler) {
    if (sheet != null) {
      firstRow = Math.max(firstRow, sheet.getFirstRowNum());
      lastRow = Math.min(lastRow, sheet.getLastRowNum());
      for (int r = firstRow; r < lastRow; r++) {
        Row row = sheet.getRow(r);
        int lCol = Math.min(lastCol, row.getLastCellNum());
        Map<Integer, Object> data = new LinkedHashMap<>();
        for (int c = Math.max(firstCol, row.getFirstCellNum()); c < lCol; c++){
          data.put(Integer.valueOf(c), readFromCell(row.getCell(c))); 
        }
        if (readHandler.read(r, data, sheet.getSheetName(), sheet)){
          break; 
        }
      } 
    } 
  }
  
  public void read(Sheet sheet, int firstRow, int firstCol, int lastRow, int lastCol, Map<String, String> columnMapping, ExcelReadWithHeaderHandler readHandler) {
    if (sheet != null) {
      firstRow = Math.max(firstRow, sheet.getFirstRowNum());
      lastRow = Math.min(lastRow, sheet.getLastRowNum());
      Map<Integer, String> columns = new HashMap<>();
      Row header = sheet.getRow(firstRow);
      int lCol = Math.min(lastCol, header.getLastCellNum());
      for (int c = Math.max(firstCol, header.getFirstCellNum()); c < lCol; c++) {
        Cell cell = header.getCell(c);
        String headerName = readFromCell(cell).toString();
        if (columnMapping != null && columnMapping.containsKey(headerName)){
          headerName = columnMapping.get(headerName); 
        }
        columns.put(Integer.valueOf(c), headerName);
      } 
      for (int r = ++firstRow; r < lastRow; r++) {
        Row row = sheet.getRow(r);
        lCol = Math.min(lastCol, row.getLastCellNum());
        Map<String, Object> data = new LinkedHashMap<>();
        for (int i = Math.max(firstCol, row.getFirstCellNum()); i < lCol; i++){
          data.put(columns.get(Integer.valueOf(i)), readFromCell(row.getCell(i))); 
        }
        if (readHandler.read(r, data, sheet.getSheetName(), sheet)){
          break; 
        }
      } 
    } 
  }
  
  public void read(ExcelReadHandler readHandler) {
    if (this.book != null) {
      for (Sheet sheet : this.book) {
        read(sheet, readHandler);
      }
    }
  }
  
  public void read(Map<String, String> columnMapping, ExcelReadWithHeaderHandler readHandler) {
    if (this.book != null){
      for (Sheet sheet : this.book){
        read(sheet, columnMapping, readHandler);  
      }
    }
  }
  
  public void read(String sheetName, ExcelReadHandler readHandler) {
    if (this.book != null) {
      Sheet sheet = this.book.getSheet(sheetName);
      read(sheet, readHandler);
    } 
  }
  
  public void read(String sheetName, Map<String, String> columnMapping, ExcelReadWithHeaderHandler readHandler) {
    if (this.book != null) {
      Sheet sheet = this.book.getSheet(sheetName);
      read(sheet, columnMapping, readHandler);
    } 
  }
  
  public void read(String sheetName, int firstRow, int firstCol, int lastRow, int lastCol, ExcelReadHandler readHandler) {
    if (this.book != null) {
      Sheet sheet = this.book.getSheet(sheetName);
      read(sheet, firstRow, firstCol, lastRow, lastCol, readHandler);
    } 
  }
  
  public void read(String sheetName, int firstRow, int firstCol, int lastRow, int lastCol, Map<String, String> columnMapping, ExcelReadWithHeaderHandler readHandler) {
    if (this.book != null) {
      Sheet sheet = this.book.getSheet(sheetName);
      read(sheet, firstRow, firstCol, lastRow, lastCol, columnMapping, readHandler);
    } 
  }
  
  public void read(int sheetIndex, ExcelReadHandler readHandler) {
    if (this.book != null) {
      Sheet sheet = this.book.getSheetAt(sheetIndex);
      read(sheet, readHandler);
    } 
  }
  
  public void read(int sheetIndex, Map<String, String> columnMapping, ExcelReadWithHeaderHandler readHandler) {
    if (this.book != null) {
      Sheet sheet = this.book.getSheetAt(sheetIndex);
      read(sheet, columnMapping, readHandler);
    } 
  }
  
  public void read(int sheetIndex, int firstRow, int firstCol, int lastRow, int lastCol, ExcelReadHandler readHandler) {
    if (this.book != null) {
      Sheet sheet = this.book.getSheetAt(sheetIndex);
      read(sheet, firstRow, firstCol, lastRow, lastCol, readHandler);
    } 
  }
  
  public void read(int sheetIndex, int firstRow, int firstCol, int lastRow, int lastCol, Map<String, String> columnMapping, ExcelReadWithHeaderHandler readHandler) {
    if (this.book != null) {
      Sheet sheet = this.book.getSheetAt(sheetIndex);
      read(sheet, firstRow, firstCol, lastRow, lastCol, columnMapping, readHandler);
    } 
  }
}
